package com.hongdee.insurance.task.service.impl;

import com.hongdee.annotation.MethodComment;
import com.hongdee.common.util.DateUtils;
import com.hongdee.insurance.task.dao.ITInsuranceOrderStatsticsTaskDao;
import com.hongdee.insurance.task.service.ITInsuranceOrderStatsticsTaskService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @author 王帅
 * @className TInsuranceOrderStatsticsTaskServiceImpl
 * @type
 * @description 保险报表统计定时任务
 * @path com.hongdee.insurance.task.service.impl
 * @create 2017/7/19 10:12
 **/
@Service
public class TInsuranceOrderStatsticsTaskServiceImpl implements ITInsuranceOrderStatsticsTaskService {

    @Autowired
    private ITInsuranceOrderStatsticsTaskDao statsticsTaskDao;

    /**
     * 统计保险返利台账
     * @throws Exception
     */
    @Override
    @MethodComment(name = "统计保险返利台账报表")
    public void statsticsMonthRebateQuartz() throws Exception {
        //1、获取当前时间
        String nowTime= DateUtils.getTimeStr();
        //2、清除当前月内的数据
        cleanNowMonthRebate(nowTime);
        //3、查询当前月数据 并 插入到统计表中
        StringBuffer sql=new StringBuffer();
        //插入语句
        sql.append("INSERT INTO t_insurance_order_statstics_rebate ( id, create_time, company_id, o_name, sb_allCommission_price, " +
                " sb_all_price, sb_back_price, sb_jqCommission_price, sb_jq_price, sb_syCommission_price, sb_sy_price, " +
                " xb_allCommission_price, xb_all_price, xb_back_price, xb_jqCommission_price, xb_jq_price, xb_syCommission_price, " +
                " xb_sy_price, statistics_time,sb_all_coupon_price,xb_all_coupon_price " +
                " ) ");
        //公司信息
        sql.append(" SELECT " +
                " REPLACE (UUID(), '-', '') id, UNIX_TIMESTAMP(NOW()) * 1000 create_time, o.*, sb.sbAllCommissionPrice, sb.sbAllPrice," +
                " sb.sbBackPrice, sb.sbJqCommissionPrice, sb.sbJqPrice, sb.sbSyCommissionPrice, sb.sbSyPrice, xb.xbAllCommissionPrice, " +
                " xb.xbAllPrice, xb.xbBackPrice, xb.xbJqCommissionPrice, xb.xbJqPrice, xb.xbSyCommissionPrice, xb.xbSyPrice, NOW()," +
                " sb.sbAllCouponPrice,xb.xbAllCouponPrice " +
                " FROM ( " +
                " SELECT company_id, o_name FROM t_insurance_order_statistics GROUP BY company_id ) o ");
        //新保信息
        sql.append(" LEFT JOIN ( " +
                " SELECT " +
                " company_id, ROUND(SUM(sb.back_Price), 2) sbBackPrice, ROUND(SUM(sb.sy_price), 2) sbSyPrice, ROUND(SUM(sb.jq_price), 2) sbJqPrice," +
                " ROUND( SUM( sb.sy_price * 1 + sb.jq_price * 1 ), 2 ) sbAllPrice, ROUND( SUM(sb.sy_commission_price), 2 ) sbSyCommissionPrice," +
                " ROUND( SUM(sb.jq_commission_price), 2 ) sbJqCommissionPrice," +
                " ROUND( SUM( sb.sy_commission_price * 1 + sb.jq_commission_price * 1 ), 2 ) sbAllCommissionPrice, " +
                " ROUND( SUM( sb.coupon_price ), 2 ) sbAllCouponPrice " +
                " FROM " +
                " t_insurance_order_statistics sb WHERE sb.insure_type = 'sb' ");
        sql.append(" and DATE_FORMAT(sb.out_order_date,'%Y%m')=DATE_FORMAT(NOW(),'%Y%m') ");
        sql.append(" GROUP BY sb.company_id " +
                " ) sb ON o.company_id = sb.company_id ");
        //续保信息
        sql.append(" LEFT JOIN ( " +
                " SELECT company_id, ROUND(SUM(xb.back_Price), 2) xbBackPrice, ROUND(SUM(xb.sy_price), 2) xbSyPrice, ROUND(SUM(xb.jq_price), 2) xbJqPrice, " +
                " ROUND( SUM( xb.sy_price * 1 + xb.jq_price * 1 ), 2 ) xbAllPrice, ROUND( SUM(xb.sy_commission_price), 2 ) xbSyCommissionPrice," +
                " ROUND( SUM(xb.jq_commission_price), 2 ) xbJqCommissionPrice," +
                " ROUND( SUM( xb.sy_commission_price * 1 + xb.jq_commission_price * 1 ), 2 ) xbAllCommissionPrice, " +
                " ROUND( SUM( xb.coupon_price ), 2 ) xbAllCouponPrice " +
                "  FROM " +
                " t_insurance_order_statistics xb " +
                " WHERE " +
                " xb.insure_type = 'xb' ");
        sql.append(" and DATE_FORMAT(xb.out_order_date,'%Y%m')=DATE_FORMAT(NOW(),'%Y%m') ");
        sql.append(" GROUP BY " +
                " xb.company_id " +
                " ) xb ON o.company_id = xb.company_id");
        this.statsticsTaskDao.executeSQL(sql.toString());
    }

    /**
     * 清除当月数据
     * @param nowTime
     * @throws Exception
     */
    private void cleanNowMonthRebate(String nowTime) throws Exception {
        String sql="delete from T_Insurance_ORDER_STATSTICS_REBATE where DATE_FORMAT(statistics_time,'%Y%m') = " +
                " DATE_FORMAT(?,'%Y%m') ";
        statsticsTaskDao.executeSQL(sql,nowTime);
    }

    @Override
    public void statsticsTotalRebate() {

    }
}
